package cn.ym.mall.dao.impl;

import cn.ym.mall.dao.StaffDao;
import cn.ym.mall.entity.Staff;
import cn.ym.mall.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StaffDaoImpl implements StaffDao {
    QueryRunner queryRunner = new QueryRunner();

    /**
     * 获得所有员工(不显示密码字段)
     * @param pageNo
     * @param pageSize
     * @return
     * @throws SQLException
     */
    @Override
    public List<Staff> getAll(Integer pageNo, Integer pageSize) throws SQLException {
        String sql="select stid, staccount, stname, sthiredate from ym_staff order by sthiredate desc limit ?,?;";
        List<Staff> staffList = new ArrayList<Staff>();
        try {
            staffList=queryRunner.query(DruidUtils.getConnection(),sql,new BeanListHandler<Staff>(Staff.class),(pageNo-1)*pageSize,pageSize);
        }catch (Exception e){
            e.printStackTrace();
        }
        return staffList;
    }

    /**
     * 添加员工
     *
     * @param staff
     * @return
     * @throws SQLException
     */
    @Override
    public int add(Staff staff) throws SQLException {
        String sql="insert into ym_staff(staccount, stpwd, stname, sthiredate) value(?,?,?,?);";
        Object[] arr={staff.getStaccount(),staff.getStpwd(),staff.getStname(),staff.getSthiredate()};
        int num=0;
        try {
            num=queryRunner.update(DruidUtils.getConnection(),sql,arr);
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }

    /**
     * 根据员工编号删除员工
     *
     * @param stid
     * @return
     * @throws SQLException
     */
    @Override
    public int delete(Integer stid) throws SQLException {
        String sql="delete from ym_staff where stid=?;";
        Object arr[]={stid};
        int num=0;
        try {
            num=queryRunner.update(DruidUtils.getConnection(),sql,arr);
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }

    /**
     * 更新员工信息
     *
     * @param staff
     * @return
     * @throws SQLException
     */
    @Override
    public int update(Staff staff) throws SQLException {
        String sql="update ym_staff set staccount=?,stpwd=?,stname=? where stid=?;";
        Object arr[]={staff.getStaccount(),staff.getStpwd(),staff.getStname(),staff.getStid()};
        int num=0;
        try {
            num=queryRunner.update(DruidUtils.getConnection(),sql,arr);
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }

    /**
     * 获得总员工数
     *
     * @return
     * @throws SQLException
     */
    @Override
    public long getCount() throws SQLException {
        long count = 0;
        try{
            String sql = "select count(0) as count from ym_staff;";
            count = queryRunner.query(DruidUtils.getConnection(),sql,new ScalarHandler<>());
        }catch (Exception e){
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 根据员工编号找员工
     *
     * @param stid
     * @return
     * @throws SQLException
     */
    @Override
    public Staff getStaffByStid(Integer stid) throws SQLException {
        String sql="select stid, staccount, stname, sthiredate from ym_staff where stid=?;";
        Object arr[] ={stid};
        Staff staff = null;
        try {
            staff=queryRunner.query(DruidUtils.getConnection(),sql,new BeanHandler<Staff>(Staff.class),arr);
        }catch (Exception e){
            e.printStackTrace();
        }
        return staff;
    }

    /**
     * 根据员工姓名找员工
     *
     * @param stname
     * @return
     * @throws SQLException
     */
    @Override
    public Staff getStaffByStname(String stname) throws SQLException {
        String sql="select stid, staccount, stname, sthiredate from ym_staff where stname=?;";
        Object arr[] ={stname};
        Staff staff = null;
        try {
            staff=queryRunner.query(DruidUtils.getConnection(),sql,new BeanHandler<Staff>(Staff.class),arr);
        }catch (Exception e){
            e.printStackTrace();
        }
        return staff;
    }

    /**
     * 根据角色id查询员工
     *
     * @param rid
     * @return
     * @throws SQLException
     */
    @Override
    public List<Staff> getStaffByRid(Integer rid,Integer PageNo,Integer PageSize) throws SQLException {
        String sql="select stid, staccount, stname, sthiredate from ym_staff where stid in (select stid from ym_role where rid = ?) order by sthiredate desc limit ?,?;";
        List<Staff> staffList = new ArrayList<Staff>();
        try {
            staffList=queryRunner.query(DruidUtils.getConnection(),sql,new BeanListHandler<Staff>(Staff.class),rid,PageNo,PageSize);
        }catch (Exception e){
            e.printStackTrace();
        }
        return staffList;
    }

    /**
     * 账号查询员工(可用于员工登录判断)
     *
     * @param staccount
     * @return
     * @throws SQLException
     */
    @Override
    public Staff getStaffByStaccount(String staccount) throws SQLException {
        String sql = "select stid, staccount, stpwd, stname, sthiredate from ym_staff where staccount=?;";
        Staff staff = null;
        try {
            staff = queryRunner.query(DruidUtils.getConnection(),sql,new BeanHandler<Staff>(Staff.class),staccount);
        }catch (Exception e){
            e.printStackTrace();
        }
        return staff;
    }

}
